<?php

setlocale(LC_MONETARY, 'en_US');
require_once "../funciones.php";
require_once '../funciones/JSON.php';
autenticado();

$caweb = con_intranetpccom();

$examp = $_REQUEST["q"]; //query number
switch ($examp) {
    case 1:
        $page = $_REQUEST['page']; // get the requested page
        $limit = $_REQUEST['rows']; // get how many rows we want to have into the grid
        $sidx = $_REQUEST['sidx']; // get index row - i.e. user click to sort
        $sord = $_REQUEST['sord']; // get the direction

        if (!$sidx)
            $sidx = 1;
        //Consulta requerimientos Creados o Pausados
        $result = mysql_query("SELECT COUNT(*) AS count FROM requerimientos req
                where not exists (select * from solucionesRq sol where sol.reqId = req.reqId)
                or exists (select * from solucionesRq sol where sol.reqId = req.reqId and sol.estRqId = 2)");
        $row = mysql_fetch_array($result, MYSQL_ASSOC);
        $count = $row['count'];

        if ($count > 0) {
            $total_pages = ceil($count / $limit);
        } else {
            $total_pages = 0;
        }
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        if ($start < 0)
            $start = 0;
        $SQL = "SELECT req.reqFec,req.reqUsr,req.reqId FROM requerimientos req
                where not exists (select * from solucionesRq sol where sol.reqId = req.reqId)
                or exists (select * from solucionesRq sol where sol.reqId = req.reqId and sol.estRqId = 2) LIMIT " . $start . " , " . $limit;
        $result = mysql_query($SQL) or die("Could not execute query." . mysql_error());

        if (stristr($_SERVER["HTTP_ACCEPT"], "application/xhtml+xml")) {
            header("Content-type: application/xhtml+xml;charset=utf-8");
        } else {
            header("Content-type: text/xml;charset=utf-8");
        }
        $et = ">";
        echo "<?xml version='1.0' encoding='utf-8'?$et\n";
        echo "<rows>";
        echo "<page>" . $page . "</page>";
        echo "<total>" . $total_pages . "</total>";
        echo "<records>" . $count . "</records>"; // be sure to put text data in CDATA$sql
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            echo "<row id='" . $row['reqId'] . "'>";
            echo "<cell>" . $row['reqId'] . "</cell>";
            echo "<cell>" . strtoupper($row['reqFec']) . "</cell>";
            echo "<cell>" . strtoupper($row['reqUsr']) . "</cell>";
            //Se adquiere la informacion de la empresa
            $sql = "select entrada from usuarios where username = '".$row['reqUsr']."'";
            $tmp = mysql_query($sql);
            $tmp = mysql_fetch_array($tmp);
            echo "<cell>" . strtoupper($tmp['entrada']) . "</cell>";
            $sql = "select max(solRqId),solRqDes,est.estRqNombre from solucionesRq sol ";
            $sql .=  "inner join estadosRq est on est.estRqId = sol.estRqId ";
            $sql .=  "where sol.estRqId = 2 and sol.reqId = ".$row['reqId']."  having max(solRqId) is not null";
            $result1 = mysql_query($sql);
            $row1 = mysql_fetch_array($result1,MYSQL_ASSOC);
            if(is_array($row1)){
                    $estado = $row['estRqNombre'];
                    $respuesta = $row1['solRqDes'];
            }
            else{
                $estado = "Creado";
                $respuesta = "";
            }
            echo "<cell>". utf8_encode($estado)."</cell>";
            echo "</row>";
        }
        echo "</rows>";
        break;


    case 2:
        //Ubico la informacion del usuario para poderla editar
        $id = trim($_REQUEST['reqId']);
        $sql = "select * from requerimientos where reqId = $id";
        $result = mysql_query($sql) or die("Could not execute query." . mysql_error());;
        $requerimiento = mysql_fetch_array($result,MYSQL_ASSOC);
        //Informacion del sistema
        $sql = "select sisNombre from sistemas where sisId = ".$requerimiento['sisId'];
        $result = mysql_query($sql) or die("Could not execute query." . mysql_error());;
        $sistema = mysql_fetch_array($result,MYSQL_ASSOC);
        //Informacion de la empresa
        $sql = "select entrada from usuarios where username = '".$requerimiento['reqUsr']."'";
        $tmp = mysql_query($sql) or die("Could not execute query." . mysql_error());;
        $empresa = mysql_fetch_array($tmp,MYSQL_ASSOC);
        //Estado del requerimiento
        $sql = "select max(solRqId),solRqDes,est.estRqNombre from solucionesRq sol ";
        $sql .=  "inner join estadosRq est on est.estRqId = sol.estRqId ";
        $sql .=  "where sol.estRqId = 2 and sol.reqId = ".$id."  having max(solRqId) is not null";
        $result1 = mysql_query($sql) or die("Could not execute query." . mysql_error());;
        $row1 = mysql_fetch_array($result1,MYSQL_ASSOC);
        if(is_array($row1)){
                $estado = $row['estRqNombre'];
                $respuesta = $row1['solRqDes'];
        }
        else{
            $estado = "Creado";
            $respuesta = "";
        }
        //Organiza el Objeto JSON para entregar
        $json = "{";
        $json .= "'reqId': '{$id}',";
        $json .= "'usuario': '".utf8_encode($requerimiento["reqUsr"])."',";
        $json .= "'fecha': '{$requerimiento["reqFec"]}',";
        $json .= "'descripcion': '".utf8_encode(htmlspecialchars($requerimiento["reqDes"]))."',";
        $json .= "'sistema': '{$sistema["sisNombre"]}',";
        $json .= "'empresa': '{$empresa["entrada"]}',";
        $json .= "'estado':    '{$estado}',"; 
        $json .= "'respuesta': '{$respuesta}'}";
        $sale = str_replace("'", "\"", $json);
        echo "$sale";
        break;
    case 3:
        require("../phpmailer/class.phpmailer.php");
        $reqId = $_POST['reqId'];
        $responsable = $_POST['responsable'];
        $sql = "select apellidos,nombres,user_email from usuarios where username = '{$_SESSION['caweb']['username']}' ";
        $result = mysql_query($sql) or die("Error sentencia SQL ".mysql_error());
        $asigno = mysql_fetch_array($result);
        $descripcion = "Asignado por ".$asigno['apellidos']." ".$asigno['nombres'];
        $sql = "insert into solucionesRq(solRqUsr,solRqDes,solRqFec,reqId,estRqId,tipRqId) 
                        values('$responsable','$descripcion',now(),$reqId,'1','1')";
        $result = mysql_query($sql) or die("Error sentencia SQL ".mysql_error());
        //optengo mail del responsable que se asigno
        $sql = "select apellidos,nombres,user_email from usuarios where username = '$responsable' ";
        $result = mysql_query($sql) or die("Error sentencia SQL ".mysql_error());
        $datosResponsable = mysql_fetch_array($result);
        register_shutdown_function(enviaCorreo($datosResponsable['user_email'],"Asignacion Requerimiento $reqId","Le ha sido asignado el requerimiento No $reqId, por favor dar tramite al mismo."));
        echo $reqId;
        break;
    default :
        break;
}
mysql_close($db);

//funcion para el envio de correos
function enviaCorreo($correoResponsable,$subjetCorreo,$describeCorreo){
    $mail = new PHPMailer();
    $mail->From     = "analista1@pc.com.co";
    $mail->FromName = "Sistema S.O.S";

    $mail->AddAddress($correoResponsable);
    $mail->WordWrap = 50;                                   // Largo de las lineas
    $mail->IsHTML(true);                                    // Podemos incluir tags html
    $mail->Subject  =  $subjetCorreo;
    $mail->Body     =  $describeCorreo;
    $mail->AltBody  =  strip_tags($mail->Body);             // Este es el contenido alternativo sin html
    //$mail->AddAttachment("/resultados/resultado.csv");      // Ingresamos la ruta del archivo

    $mail->IsSMTP();
    $mail->Host       = "ssl://smtp.gmail.com";
    $mail->SMTPDebug  = 1;
    $mail->SMTPAuth   = true;
    $mail->Port       = 465;
    $mail->Username   = "analista1@pc.com.co";
    $mail->Password   = "leugim";
    $mail->Send();
}
?>